package system.base;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.Vector;

import system.data.StorageMonthOutPageData;
import framework.core.DBConnection;
import framework.core.DBEngine;
import framework.core.DBPreparedStatement;
import framework.core.DBResult;
import framework.core.DateUtil;
import framework.core.IHandler;
import framework.core.ILoginContext;
import framework.core.Logger;
import framework.core.PageData;
import framework.core.SearchEvent;
import framework.core.SrvContext;
import framework.core.StringUtil;

/*
 Author    :Benny
 CreateTime:2008-05-13 14:42:07
 */
public class StorageMonthOutSrv extends IHandler {

    public StorageMonthOutSrv() {
        super();
    }

    private void deleteStorageMonthOut(DBConnection con, SrvContext context) throws Exception {
        String[] ids = context.getParameterValues("ids");
        StorageMonthOut obj = null;
        for (int i = 0; i < ids.length; i++) {
            obj = StorageMonthOut.fetch(con, ids[i]);
            if (obj != null)
                obj.delete(con);
        }
    }

    private void saveStorageMonthOut(DBConnection con, SrvContext context, ILoginContext loginContext) throws Exception {
        String id = context.getString("Id", null);
        StorageMonthOut obj;
        if ((id == null) || (id.equals(""))) // add
        {
            obj = StorageMonthOut.fetch();
        } else // update
        {
            obj = StorageMonthOut.fetch(con, id);
        }
        StorageMonthOutPageData data = (StorageMonthOutPageData) PageData.fromForm(context, StorageMonthOutPageData.class);
        context.setValue("StorageMonthOutPageData", data);
        obj.fill(data);
        obj.save(con);
    }

    

    private void backStorageMonthOut(DBConnection con, SrvContext context, String path) throws Exception {
        context.setNextUrl(path + "StorageMonthOutList.jsp");
    }

    public void doAction(DBConnection con, SrvContext context, ILoginContext loginContext) throws Exception {
        String action_code = context.getActionCode();
        String path = "/system/base/";

        if (action_code.equalsIgnoreCase("listStorageMonthOut")) {
            listStorageMonthOut(con, context, path);
            return;
        }
        if (action_code.equalsIgnoreCase("viewStorageMonthOut")) {
            String id = context.getString("Id", null);
            StorageMonthOut obj = StorageMonthOut.fetch(con, id);
            context.setValue("StorageMonthOut", obj);
            context.setNextUrl(path + "StorageMonthOutView.jsp");
            return;
        }
        if (action_code.equalsIgnoreCase("deleteStorageMonthOut")) {
            context.setErrorUrl(path + "StorageMonthOutList.jsp");
            deleteStorageMonthOut(con, context);
            backStorageMonthOut(con, context, path);
            return;
        }

        if (action_code.equalsIgnoreCase("toAddStorageMonthOut")) {
            StorageMonthOut obj = StorageMonthOut.fetch();
            StorageMonthOutPageData data = (StorageMonthOutPageData) PageData.fromTable(obj, StorageMonthOutPageData.class);
            context.setValue("StorageMonthOutPageData", data);
            context.setNextUrl(path + "StorageMonthOutEdit.jsp");
            return;
        }
        if (action_code.equalsIgnoreCase("toUpdateStorageMonthOut")) {
            String id = context.getString("Id", null);
            StorageMonthOut obj = StorageMonthOut.fetch(con, id);
            StorageMonthOutPageData data = (StorageMonthOutPageData) PageData.fromTable(obj, StorageMonthOutPageData.class);
            context.setValue("StorageMonthOutPageData", data);
            context.setNextUrl(path + "StorageMonthOutEdit.jsp");
            return;
        }
        if (action_code.equalsIgnoreCase("toCopyStorageMonthOut")) {
            String id = context.getString("Id", null);
            StorageMonthOut obj = StorageMonthOut.copy(StorageMonthOut.fetch(con, id));
            StorageMonthOutPageData data = (StorageMonthOutPageData) PageData.fromTable(obj, StorageMonthOutPageData.class);
            context.setValue("StorageMonthOutPageData", data);
            context.setNextUrl(path + "StorageMonthOutEdit.jsp");
            return;
        }
        if (action_code.equalsIgnoreCase("saveStorageMonthOut")) {
            context.setErrorUrl(path + "StorageMonthOutEdit.jsp");
            saveStorageMonthOut(con, context, loginContext);
            backStorageMonthOut(con, context, path);
            return;
        }
        if (action_code.equalsIgnoreCase("saveAndNewStorageMonthOut")) {
            context.setErrorUrl(path + "StorageMonthOutEdit.jsp");
            saveStorageMonthOut(con, context, loginContext);
            StorageMonthOut obj = StorageMonthOut.fetch();
            StorageMonthOutPageData data = (StorageMonthOutPageData) PageData.fromTable(obj, StorageMonthOutPageData.class);
            context.setValue("StorageMonthOutPageData", data);
            context.setNextUrl(path + "StorageMonthOutEdit.jsp");
            return;
        }
        if (action_code.equalsIgnoreCase("backListStorageMonthOut")) {
            backStorageMonthOut(con, context, path);
            return;
        }

        // add by Benny start

        // add by Benny end

        Logger.throwException("StorageMonthOutSrv", "unknown action_code=" + action_code);
    }
    
    
    /*****************************************新增管理页面功能方法*************************************************/
    // 单一公司费用月统计
    private Vector search5001(String sq5001, DBConnection con, String wareHouseCode, String balanceTime) throws Exception {
        Vector vec = new Vector();
        DBResult dbs = DBEngine.doSelect(con, sq5001);
        ResultSet rs = dbs.getRs();
        while (rs.next()) {
            StorageMonthOutPageData data = new StorageMonthOutPageData();
            data.setProductMoney(String.valueOf(rs.getDouble(1)));
            data.setWarehouseCode(wareHouseCode,con);
            data.setBalanceTime(balanceTime);
            vec.add(data);
        }
        rs.close();
        dbs.clear();
        return vec;
    }

    // 单一公司费用月分类统计
    private Vector search5002(String sql5002, DBConnection con, String wareHouseCode, String balanceTime) throws Exception {
        Vector vec = new Vector();
        DBResult dbs = DBEngine.doSelect(con, sql5002);
        ResultSet rs = dbs.getRs();
        while (rs.next()) {
            StorageMonthOutPageData data = new StorageMonthOutPageData();
            data.setWarehouseCode(wareHouseCode,con);
            data.setBalanceTime(balanceTime);
            data.setCategoryCode(rs.getString(1));
            data.setProductMoney(String.valueOf(rs.getDouble(2)));
            vec.add(data);
        }
        rs.close();
        dbs.clear();
        return vec;
    }

    // 单一公司 指定一年中 每月 费用
    private Vector search5003(String sql5003, DBConnection con, String wareHouseCode) throws Exception {
        // 保存月份的集合
        Vector vec = new Vector();
        // 查询出符合条件的月份
        DBResult dbs = DBEngine.doSelect(con, sql5003);
        ResultSet rs = dbs.getRs();
        while (rs.next()) {
            StorageMonthOutPageData data = new StorageMonthOutPageData();
            data.setWarehouseCode(wareHouseCode,con);
            data.setBalanceTime(rs.getString(1));
            data.setProductMoney(String.valueOf(rs.getDouble(2)));
            vec.add(data);
        }
        rs.close();
        dbs.clear();
        return vec;
    }

    // 单一公司 指定一年中 每月 分类 费用
    private Vector search5004(String sql5004, DBConnection con, String wareHouseCode) throws Exception {
        // 保存月份的集合
        Vector vec = new Vector();
        // 查询出符合条件的月份
        DBResult dbs = DBEngine.doSelect(con, sql5004);
        ResultSet rs = dbs.getRs();
        while (rs.next()) {
            StorageMonthOutPageData data = new StorageMonthOutPageData();
            data.setWarehouseCode(wareHouseCode,con);
            data.setBalanceTime(rs.getString(1));
            data.setCategoryCode(rs.getString(3));
            data.setProductMoney(String.valueOf(rs.getDouble(2)));
            vec.add(data);
        }
        rs.close();
        dbs.clear();
        return vec;
    }
    //单一公司 指定一年中 发生出库费用的产品大类
    private Vector search5004Category(String sql5004Category, DBConnection con) throws Exception {
        Vector vec = new Vector();
        DBResult dbs = DBEngine.doSelect(con, sql5004Category);
        ResultSet rs = dbs.getRs();
        while(rs.next()){
            String code = rs.getString(1);
            vec.add(code);
        }
        rs.close();
        dbs.clear();
        return vec;
    }
    private Vector cal5004Result(Vector codes, Vector vec) throws Exception {
        Vector vecResult = new Vector();
        if(codes == null) return vecResult;
        if(vec == null) return vecResult;
        if(codes.size() <= 0) return vecResult;
        if(vec.size() <= 0) return vecResult;
        
        for(int i = 0; i < codes.size(); i++){
            String code = (String)codes.get(i);
            String[] result = new String[13];
            result[0] = code;
            for(int i1 = 1; i1 < 13; i1++){
                result[i1] = "0";
            }
            
            for(int i1 = 1; i1 < 13; i1++){
                String month;
                if(i1 < 10) month = "0" + i1;
                else month = "" + i1;
                
                for(int j = 0; j < vec.size(); j++){
                    StorageMonthOutPageData data = (StorageMonthOutPageData)vec.get(j);
                    if(code.equals(data.getCategoryCode()) && month.equals(data.getBalanceTime())){
                        result[i1] = data.getProductMoney();
                    }
                }
            }
            
            vecResult.add(result);
        }
        
        return vecResult;
    }

    private Vector search5005(String sql5005, DBConnection con, String balanceTime) throws Exception {
        Vector vec = new Vector();
        DBResult dbs = DBEngine.doSelect(con, sql5005);
        ResultSet rs = dbs.getRs();
        while (rs.next()) {
            StorageMonthOutPageData data = new StorageMonthOutPageData();
            data.setProductMoney(String.valueOf(rs.getDouble(2)));
            data.setWarehouseCode(rs.getString(1),con);
            data.setBalanceTime(balanceTime);
            vec.add(data);
        }
        rs.close();
        dbs.clear();
        return vec;
    }
    
    private Vector searchWarehouse(DBConnection con, String sql5006_warehouse) throws Exception {
        Vector vec = new Vector();
        DBResult dbs = DBEngine.doSelect(con, sql5006_warehouse);
        ResultSet rs = dbs.getRs();
        while (rs.next()) {
            String code = rs.getString(1);
            vec.add(code);
        }
        rs.close();
        dbs.clear();
        return vec;
    }

    private Vector search5006(String sql5006, DBConnection con, String balanceTime) throws Exception {
        Vector vec = new Vector();
        DBResult dbs = DBEngine.doSelect(con, sql5006);
        ResultSet rs = dbs.getRs();
        while (rs.next()) {
            StorageMonthOutPageData data = new StorageMonthOutPageData();
            data.setWarehouseCode(rs.getString(1),con);
            data.setBalanceTime(balanceTime);
            data.setCategoryCode(rs.getString(2));
            data.setProductMoney(String.valueOf(rs.getDouble(3)));
            vec.add(data);
        }
        rs.close();
        dbs.clear();
        return vec;
    }
    
    private Vector cal5006(Vector vecWarehouse, Vector vec5006) throws Exception {
        Vector vec = new Vector();
        if(vecWarehouse == null) return vec;
        if(vec5006 == null) return vec;
        if(vecWarehouse.size() < 1) return vec;
        
        for(int ia = 0; ia < vecWarehouse.size(); ia++){
            String wcode = (String)vecWarehouse.get(ia);
            Vector wVec = new Vector();
            for(int i = 0; i < vec5006.size(); i++){
                StorageMonthOutPageData data = (StorageMonthOutPageData)vec5006.get(i);
                if(wcode.equals(data.getWarehouseCode())){
                    wVec.add(data);
                }
            }
            if(wVec.size() > 0) vec.add(wVec);
        }
        
        return vec;
    }

    private Vector search5007(String sql5007, DBConnection con, String balanceTime) throws Exception {
        Vector vec = new Vector();
        DBResult dbs = DBEngine.doSelect(con, sql5007);
        ResultSet rs = dbs.getRs();
        while (rs.next()) {
            StorageMonthOutPageData data = new StorageMonthOutPageData();
            data.setWarehouseCode(rs.getString(1),con);
            data.setBalanceTime(rs.getString(2));
            data.setProductMoney(String.valueOf(rs.getDouble(3)));
            vec.add(data);
        }
        rs.close();
        dbs.clear();
        return vec;
    }
    
    private Vector cal5007Result(Vector warehouseCodes, Vector vec) throws Exception {
        Vector vecResult = new Vector();
        if(warehouseCodes == null) return vecResult;
        if(vec == null) return vecResult;
        if(warehouseCodes.size() <= 0) return vecResult;
        if(vec.size() <= 0) return vecResult;
        
        for(int i = 0; i < warehouseCodes.size(); i++){
            String warehouseCode = (String)warehouseCodes.get(i);
            String[] result = new String[13];
            result[0] = warehouseCode;
            for(int i1 = 1; i1 < 13; i1++){
                result[i1] = "0";
            }
            
            for(int i1 = 1; i1 < 13; i1++){
                String month;
                if(i1 < 10) month = "0" + i1;
                else month = "" + i1;
                
                for(int j = 0; j < vec.size(); j++){
                    StorageMonthOutPageData data = (StorageMonthOutPageData)vec.get(j);
                    if(warehouseCode.equals(data.getWarehouseCode()) && month.equals(data.getBalanceTime())){
                        result[i1] = data.getProductMoney();
                    }
                }
            }
            
            vecResult.add(result);
        }
        
        return vecResult;
    }
    
    private Vector cal5008Result(Vector warehouseCodes, Vector vec) throws Exception {
        Vector vecResult = new Vector();
        if(warehouseCodes == null) return vecResult;
        if(vec == null) return vecResult;
        if(warehouseCodes.size() <= 0) return vecResult;
        if(vec.size() <= 0) return vecResult;
        
        for(int i = 0; i < warehouseCodes.size(); i++){
            String warehouseCode = (String)warehouseCodes.get(i);
            Vector wVec = new Vector();
            Vector category = searchCategory(vec, warehouseCode);
            for(int n = 0; n < category.size(); n++){
                String categoryCode = (String)category.get(n);
                String[] result = new String[14];
                result[0] = warehouseCode;
                result[13] = categoryCode; 
                for(int i1 = 1; i1 < 13; i1++){
                    result[i1] = "0";
                }
            
                for(int i1 = 1; i1 < 13; i1++){
                    String month;
                    if(i1 < 10) month = "0" + i1;
                        else month = "" + i1;
                
                    for(int j = 0; j < vec.size(); j++){
                        StorageMonthOutPageData data = (StorageMonthOutPageData)vec.get(j);
                        if(warehouseCode.equals(data.getWarehouseCode()) && month.equals(data.getBalanceTime()) && categoryCode.equals(data.getCategoryCode())){
                            result[i1] = data.getProductMoney();
                        }
                    }
                }
            
                wVec.add(result);
            }
            
            if(wVec.size() > 0) vecResult.add(wVec);
        }    
        
        return vecResult;
    }


    private Vector search5008(String sql5008, DBConnection con, String balanceTime) throws Exception {
        Vector vec = new Vector();
        DBResult dbs = DBEngine.doSelect(con, sql5008);
        ResultSet rs = dbs.getRs();
        while (rs.next()) {
            StorageMonthOutPageData data = new StorageMonthOutPageData();
            data.setWarehouseCode(rs.getString(1),con);
            data.setBalanceTime(rs.getString(2));
            data.setCategoryCode(rs.getString(4));
            data.setProductMoney(String.valueOf(rs.getDouble(3)));
            vec.add(data);
        }
        rs.close();
        dbs.clear();
        return vec;
    }
    
    private Vector searchCategory(Vector vecs, String warehouseCode) throws Exception{
        Vector vec = new Vector();
        if(vecs == null) return vec;
        if(vecs.size() < 1) return vec;
        if(warehouseCode == null) return vec;
        if("".equals(warehouseCode)) return vec;
        
        for(int i = 0; i < vecs.size(); i++){
            StorageMonthOutPageData pd = (StorageMonthOutPageData)vecs.get(i);
            String category = pd.getCategoryCode();
            if(category == null) category = "";
            if(warehouseCode.equals(pd.getWarehouseCode())){
                if(vec.size() < 1){
                    if(!"".equals(category)){
                        vec.add(category);
                    }
                }
                else{
                    boolean bz = false;
                    for(int j = 0; j < vec.size(); j++){
                        String c = (String)vec.get(j);
                        if(c.equals(category)){
                            bz = true;
                            break;
                        }
                    }
                    if(!"".equals(category) && bz == false){
                        vec.add(category);
                    }
                }
            }
        }

        return vec;
    }

    /** 查   类别  名称*/
    public static String searcheCategoryName(DBConnection con, String code)throws Exception{
        String sql = "select name from t_product_category where category_level = '1' and t_product_category.code = '"+code+"'";
        DBResult dbs = DBEngine.doSelect(con, sql);
        ResultSet rs = dbs.getRs();
        String name = "";
        while (rs.next()) {
            name = rs.getString(1);
        }
        rs.close();
        dbs.clear();
        if(name == null || name == "" ){
            name = "其他";
        }
        return name;
    }
    
    private boolean checkFirst(SrvContext context) throws Exception{
        boolean isfirst = context.getBoolean("isfirst");
        return isfirst;
    }
    
    private void listStorageMonthOut(DBConnection con, SrvContext context, String path) throws Exception {
        StorageMonthOutSearchEvent event = (StorageMonthOutSearchEvent)SearchEvent.fromForm(context,StorageMonthOutSearchEvent.class);
        String wareHouseCode = "";
        String balanceTime = "";
        DateUtil du = new DateUtil();
        String initDateMonth = du.getFullString().substring(0, 7);
        //String initDateYear = du.getFullString().substring(0, 4);
        boolean isfirst = checkFirst(context);
        if(isfirst){
            event.setBalanceTime(initDateMonth);
            event.setWarehouseCode("000000");
            wareHouseCode = "000000";
            balanceTime = initDateMonth;
        }else{
            wareHouseCode = context.getString("s_WarehouseCode") == null ? "" : context.getString("s_WarehouseCode");
            balanceTime = context.getString("s_BalanceTime") == null ? "" : context.getString("s_BalanceTime");
        }
        context.setSessionValue("StorageMonthOutSearchEvent",event);
        context.setSessionValue("isfirst",Boolean.valueOf(isfirst));
        String menuJsp = null;
        
        //单一公司月费用查询--------------------------------------------------------------------------------
        String sql5001_sub = "select * from t_out_sub sub " +
                             "left outer join t_out_head head on sub.out_bill_code = head.out_bill_code " +
                             "where " +
                             "warehouse_code = '"+ wareHouseCode + "' " +
                             "and sub.status = '1' " +
                             "and substr(out_time, 1, 7) = '" + balanceTime + "' "; 
        String sql5001 = "select sum(product_money) from ( " + sql5001_sub + ")";
       //单一公司月费用查询--------------------------------------------------------------------------------
        
        
       //单一公司分类月费用查询--------------------------------------------------------------------------------
        String sql5002_sub = "select sub.*, substr(product.category_code, 1,1)||'0000' category_code from t_out_sub sub " +
                             "left outer join t_out_head head on sub.out_bill_code = head.out_bill_code " +
                             "left outer join t_product product on sub.product_id = product.id " +
                             "where " +
                             "head.warehouse_code = '" + wareHouseCode + "' " +
                             "and product.category_code <> '90001' " +
                             "and sub.status = '1' " +
                             "and substr(head.out_time, 1, 7) = '" + balanceTime + "' " +
                             
                             "union all " + 
                             
                             "select sub.*, product.category_code from t_out_sub sub " +
                             "left outer join t_out_head head on sub.out_bill_code = head.out_bill_code " +
                             "left outer join t_product product on sub.product_id = product.id " +
                             "where " +
                             "head.warehouse_code = '" + wareHouseCode + "' " +
                             "and product.category_code = '90001' " +
                             "and sub.status = '1' " +
                             "and substr(head.out_time, 1, 7) = '" + balanceTime + "' ";
        String sql5002 = "select category_code,sum(product_money) from (" + sql5002_sub + ") group by category_code order by category_code";
       //单一公司分类月费用查询--------------------------------------------------------------------------------
        
        
      //单一公司按月费用查询--------------------------------------------------------------------------------
        String balanceYear = balanceTime;
        if(balanceYear.length() > 4) balanceYear = balanceYear.substring(0, 4);
        String sql5003_sub = "select substr(head.out_time, 6, 2) balance_time, sub.product_money from t_out_sub sub " +
                             "left outer join t_out_head head on sub.out_bill_code = head.out_bill_code " +
                             "where " +
                             "head.warehouse_code = '" + wareHouseCode + "' " +
                             "and sub.status = '1' " +
                             "and substr(head.out_time, 1, 4) = '" + balanceYear + "'";
        String sql5003 = "select balance_time, sum(product_money) from (" + sql5003_sub + ") group by balance_time order by balance_time";
      //单一公司按月费用查询--------------------------------------------------------------------------------
        
      //单一公司按月分类费用查询--------------------------------------------------------------------------------
        balanceYear = balanceTime;
        String sql5004_category = "select a.category_code from ( " +
                                     "select sum(sub.product_money) money, substr(product.category_code, 1,1)||'0000' category_code from t_out_sub sub " +
                                       "left outer join t_out_head head on sub.out_bill_code = head.out_bill_code " +
                                       "left outer join t_product product on sub.product_id = product.id " +
                                     "where " +
                                        "head.warehouse_code = '"+ wareHouseCode + "' " +
                                        "and product.category_code <> '90001' " +
                                        "and sub.status = '1' " +
                                        "and substr(head.out_time, 1, 4) = '" + balanceYear + "' " +
                                      "group by " +
                                         "substr(product.category_code, 1,1) " +

                                       " union all " +

                                       "select sum(sub.product_money) , category_code from t_out_sub sub " +
                                          "left outer join t_out_head head on sub.out_bill_code = head.out_bill_code " +
                                          "left outer join t_product product on sub.product_id = product.id " +
                                       "where " +
                                          "head.warehouse_code = '" + wareHouseCode + "' " +
                                          "and product.category_code = '90001' " +
                                          "and sub.status = '1' " +
                                          "and substr(head.out_time, 1, 4) = '" + balanceYear + "' " +
                                       "group by " +
                                          "category_code " +

                                      ") a " +
                                     "where " +
                                        "a.money > 0 " +
                                     "order by " +
                                         "category_code";
      
        String sql5004_sub1 = "select substr(head.out_time, 6, 2) balance_time, sum(sub.product_money) money, substr(product.category_code, 1,1)||'0000' category_code from t_out_sub sub " +
                                 "left outer join t_out_head head on sub.out_bill_code = head.out_bill_code " +
                                 "left outer join t_product product on sub.product_id = product.id " +
                              "where " +
                                 "head.warehouse_code = '" + wareHouseCode + "' " +
                                 "and product.category_code <> '90001' " +
                                 "and sub.status = '1' " +
                                 "and substr(head.out_time, 1, 4) = '" + balanceYear + "' " +
                              "group by " +
                                 "substr(head.out_time, 6, 2), " +
                                 "substr(product.category_code, 1,1) "; 
        String sql5004_sub2 = "select substr(head.out_time, 6, 2) balance_time, sum(sub.product_money) money, product.category_code from t_out_sub sub " +
                                 "left outer join t_out_head head on sub.out_bill_code = head.out_bill_code " +
                                 "left outer join t_product product on sub.product_id = product.id " +
                              "where " +
                                 "head.warehouse_code = '" + wareHouseCode + "' " +
                                 "and product.category_code = '90001' " +
                                 "and sub.status = '1' " +
                                 "and substr(head.out_time, 1, 4) = '" + balanceYear + "' " +
                              "group by " +
                                 "substr(head.out_time, 6, 2), " +
                                 "product.category_code "; 
        String sql5004 = "select * from (" + sql5004_sub1 + " union all " + sql5004_sub2 + ") a order by a.balance_time, a.category_code";
      //单一公司按月分类分类型费用查询-------------------------------------------------------------------------------- 
        
        
      //所有公司月费用查询-------------------------------------------------------------------------------- 
        String sql5005_1 = "select code, 0 money from t_warehouse";
        String sql5005_2 = "select warehouse_code, sum(product_money) money from ( " +
                           "select * from t_out_sub sub " + 
                              "left outer join t_out_head head on sub.out_bill_code = head.out_bill_code " +
                              "where " +
                                 "substr(out_time, 1, 7) = '" + balanceTime + "' " +
                                 "and sub.status = '1' " +
                           ") " +
                           "group by warehouse_code";
        String sql5005 = "select code, sum(money) total_money from ( " +
                         sql5005_1 + " union all " + sql5005_2 + " ) a " +
                         "group by code order by code";
      //所有公司月费用查询--------------------------------------------------------------------------------
        
      //所有公司分类月费用查询--------------------------------------------------------------------------------
        balanceYear = balanceTime; 
        String sql5006_1 = "select head.warehouse_code, sub.*, substr(product.category_code, 1,1)||'0000' category_code from t_out_sub sub " +
                              "left outer join t_out_head head on sub.out_bill_code = head.out_bill_code " +
                              "left outer join t_product product on sub.product_id = product.id " +
                           "where " +
                           "substr(head.out_time, 1, 7) = '" + balanceYear + "' " +
                           "and sub.status = '1' " +
                           "and product.category_code <> '90001' ";
        String sql5006_2 = "select head.warehouse_code, sub.*, category_code from t_out_sub sub " +
                              "left outer join t_out_head head on sub.out_bill_code = head.out_bill_code " +
                              "left outer join t_product product on sub.product_id = product.id " +
                           "where " +
                              "substr(head.out_time, 1, 7) = '" + balanceYear + "' " +
                              "and sub.status = '1' " +
                              "and product.category_code = '90001' ";
        
        String sql5006 = "select warehouse_code, category_code, sum(product_money) from ( " +
                            sql5006_1 + " union all " + sql5006_2 + " ) " +
                          "group by warehouse_code, category_code " +
                          "order by warehouse_code, category_code";
        
        String sql5006_warehouse = "select code from t_warehouse";
       //所有公司分类月费用查询--------------------------------------------------------------------------------
        
       //所有公司年度按月统计-----------------------------------------------------------------------------------
        balanceYear = balanceTime;
        if(balanceYear.length() > 4) balanceYear = balanceYear.substring(0, 4);
        String sql5007_sub = "select head.warehouse_code,substr(head.out_time, 6, 2) data_month, sub.product_money from t_out_sub sub " +
                                "left outer join t_out_head head on sub.out_bill_code = head.out_bill_code " +
                             "where " +
                             "sub.status = '1' " +
                             "and substr(head.out_time, 1, 4) = '" + balanceYear + "' ";
        
        String sql5007 = "select warehouse_code, data_month, sum(product_money) from ( " + sql5007_sub + " ) " +
                         " group by warehouse_code, data_month " +
                         " order by warehouse_code, data_month ";
        
        String sql5007_warehouse = "select code, 0 money from t_warehouse order by code";
       //所有公司年度按月统计-----------------------------------------------------------------------------------
       
      //所有公司年度按月按分类统计-----------------------------------------------------------------------------------
        balanceYear = balanceTime;
        if(balanceYear.length() > 4) balanceYear = balanceYear.substring(0, 4);
        String sql5008_1 = "select head.warehouse_code, substr(head.out_time, 6, 2) balance_time, sum(sub.product_money) money, substr(product.category_code, 1,1)||'0000' category_code from t_out_sub sub " +
                              "left outer join t_out_head head on sub.out_bill_code = head.out_bill_code " +
                              "left outer join t_product product on sub.product_id = product.id " +
                           "where " +
                              "product.category_code <> '90001' " +
                              "and sub.status = '1' " +
                              "and substr(head.out_time, 1, 4) = '" + balanceYear + "' " +
                           "group by " +
                              "head.warehouse_code, substr(head.out_time, 6, 2), substr(product.category_code, 1,1) ";
        
        String sql5008_2 = "select head.warehouse_code, substr(head.out_time, 6, 2) balance_time, sum(sub.product_money) , product.category_code from t_out_sub sub " +
                              "left outer join t_out_head head on sub.out_bill_code = head.out_bill_code " +
                              "left outer join t_product product on sub.product_id = product.id " +
                           "where " +
                              "product.category_code = '90001' " +
                              "and sub.status = '1' " +
                              "and substr(head.out_time, 1, 4) = '" + balanceYear + "' " +
                           "group by " +
                              "head.warehouse_code, " +
                              "substr(head.out_time, 6, 2), " +
                              "product.category_code ";

        
        String sql5008 = "select * from ( " + sql5008_1 + " union all " + sql5008_2 + ") a order by warehouse_code, a.balance_time, a.category_code ";
      //所有公司年度按月按分类统计-----------------------------------------------------------------------------------
        
        
        // 获得导航编码
        String menuCode = context.getString("menu");
        if(menuCode == null || menuCode == ""){
            menuCode = (String)context.getSessionValue("choice");
        }else{
            // 为返回请求确定菜单方法
            context.setSessionValue("choice", menuCode);
        }
        if(balanceTime != null || balanceTime != ""){
            context.setSessionValue("s_BalanceTime", balanceTime);
        }
        // 转换为整形
        int menu = Integer.parseInt(menuCode);
        // 判断导航所需执行方法
        switch (menu) {
        // 指定运营公司 某指定月中 所有 材料(不分类) 费用
        case 5001:
            menuJsp = "StorageMonthOutList5001.jsp";        
            //if(!checkFirst(context)){
                Vector vec5001 = this.search5001(sql5001, con, wareHouseCode, balanceTime);
                context.setSessionValue(menuCode, vec5001);// 指定仓库金额
            //}
            context.setNextUrl(path + menuJsp);   
            break;
        // 指定运营公司 某指定月中 所有 材料分类 费用
        case 5002:
            menuJsp = "StorageMonthOutList5002.jsp";
            if(!checkFirst(context)){
                Vector vec5002 = this.search5002(sql5002, con, wareHouseCode, balanceTime);
                context.setSessionValue(menuCode, vec5002);// 获得指定公司 某月 费用分类明细
            }
            context.setNextUrl(path + menuJsp);
            break;
        // 指定运营公司 某指定年中 所有 材料(不分类) 费用
        case 5003:
            menuJsp = "StorageMonthOutList5003.jsp";
            if(!checkFirst(context)){
                Vector vec5003 = this.search5003(sql5003, con, wareHouseCode);
                context.setSessionValue(menuCode, vec5003);
            }
            context.setNextUrl(path + menuJsp);
            break;
        // 单一公司费用某年月分类明细
        case 5004:
            menuJsp = "StorageMonthOutList5004.jsp";
            if(!checkFirst(context)){
                Vector vec5004 = this.search5004(sql5004, con, wareHouseCode);
                Vector vecCode = this.search5004Category(sql5004_category, con);
                Vector results = this.cal5004Result(vecCode, vec5004);
                context.setSessionValue(menuCode, results);
            }
            context.setNextUrl(path + menuJsp);
            break;
        // 所有运营公司 某指定月中 所有 材料(不分类) 费用
        case 5005:
            menuJsp = "StorageMonthOutList5005.jsp";
            if(!checkFirst(context)){
                Vector vec5005 = this.search5005(sql5005, con, balanceTime);
                context.setSessionValue(menuCode, vec5005);
            }
            context.setNextUrl(path + menuJsp);
            break;
        // 所有运营公司 某指定月中 所有 材料分类 费用
        case 5006:
            menuJsp = "StorageMonthOutList5006.jsp";
            if(!checkFirst(context)){
                Vector vec5006 = this.search5006(sql5006, con, balanceTime);
                Vector vecWarehouse = this.searchWarehouse(con, sql5006_warehouse);
                Vector vecResult = this.cal5006(vecWarehouse, vec5006);
                context.setSessionValue(menuCode, vecResult);
            }
            context.setNextUrl(path + menuJsp);
            break;
        // 所有运营公司 某指定年中 所有 材料(不分类) 费用
        case 5007:
            menuJsp = "StorageMonthOutList5007.jsp";
            if(!checkFirst(context)){
                Vector vec5007 = this.search5007(sql5007, con, balanceTime);
                Vector vecWarehouse = this.searchWarehouse(con, sql5007_warehouse);
                Vector results = this.cal5007Result(vecWarehouse, vec5007);
                context.setSessionValue(menuCode, results);
            }
            context.setNextUrl(path + menuJsp);
            break;
        // 所有运营公司 某指定年中 所有 材料分类 费用
        case 5008:
            menuJsp = "StorageMonthOutList5008.jsp";
            if(!checkFirst(context)){
                Vector vec5008 = this.search5008(sql5008, con, balanceTime);
                Vector vecWarehouse = this.searchWarehouse(con, sql5007_warehouse);
                Vector result = cal5008Result(vecWarehouse, vec5008);
                context.setSessionValue(menuCode, result);
            }
            context.setNextUrl(path + menuJsp);
            break;
            //
        case 5009:
            menuJsp = "StorageMonthOutList5009.jsp";
            if(!checkFirst(context)){
                double[] moneys = sumMoney5009(wareHouseCode, balanceTime.substring(0,4), con);
                context.setSessionValue(menuCode, moneys);
            }
            context.setNextUrl(path + menuJsp);
            break;
            //
        case 5010:
            menuJsp = "StorageMonthOutList5010.jsp";
            if(!checkFirst(context)){
                String[] categoryCodes = getCategoryCodes(wareHouseCode,balanceTime.substring(0,4), con);
                Vector vec = new Vector();
                for (int i = 0; i < categoryCodes.length; i++) {
                    Map map = new HashMap();
                    String[] moneys = sumMoney5010(balanceTime.substring(0,4), categoryCodes[i], con,wareHouseCode);
                    map.put("cate", categoryCodes[i]);
                    map.put("value", moneys);
                    vec.add(map);
                }
                context.setSessionValue(menuCode, vec);
            }
            context.setNextUrl(path + menuJsp);
            break;
            //
        case 5011:
            menuJsp = "StorageMonthOutList5011.jsp";
            if(!checkFirst(context)){
                double[] moneys = sumMoney5011(balanceTime.substring(0,4), con);
                context.setSessionValue(menuCode, moneys);
            }
            context.setNextUrl(path + menuJsp);
            break;
            //
        case 5012:
            menuJsp = "StorageMonthOutList5012.jsp";
            if(!checkFirst(context)){
                String[] categoryCodes = getCategoryCodes(balanceTime.substring(0,4), con);
                Vector vec = new Vector();
                for (int i = 0; i < categoryCodes.length; i++) {
                    Map map = new HashMap();
                    String[] moneys = sumMoney5012(balanceTime.substring(0,4), categoryCodes[i], con);
                    map.put("cate", categoryCodes[i]);
                    map.put("value", moneys);
                    vec.add(map);
                }
                context.setSessionValue(menuCode, vec);
            }
            context.setNextUrl(path + menuJsp);
            break;
        }
    }
    
    /** 获取一个集合内 拥有的 仓库数组    (名称)    元素唯一*/
    public static String[] getWarehouseNames(Vector vec){
        if(vec == null || vec.size() == 0) return null;
        int size = vec.size();
        String[] str = new String[size];
        Vector v1 = new Vector();
        for (int i = 0; i < size; i++) {
            StorageMonthOutPageData data =  (StorageMonthOutPageData)vec.get(i);
            str[i] = data.getWarehouseName();
        }
        String[] warehouseNames = setStrArray(str);
        return warehouseNames;
    }
    
    /** 获取一个集合内 指定仓库的   指定时间的(NULL代表不以时间为判断单位)   "类别"数组  (编号)    元素唯一*/
    public static String[] getCategoryCodes(Vector vec,String warehouseName,String balanceTime){
        if(vec == null || vec.size() == 0) return null;
        int size = vec.size();
        Vector v1 = new Vector();
        if(balanceTime == null){
            for (int i = 0; i < size; i++) {
                StorageMonthOutPageData data =  (StorageMonthOutPageData)vec.get(i);
                if(data.getWarehouseName().equals(warehouseName)){
                    v1.add(data.getWarehouseName());
                }
            }
        }else{
            for (int i = 0; i < size; i++) {
                StorageMonthOutPageData data =  (StorageMonthOutPageData)vec.get(i);
                if(data.getWarehouseName().equals(warehouseName)&&data.getBalanceTime().equals(balanceTime)){
                    v1.add(data.getCategoryCode());
                }
            }
        }
        String[] str = setStrArray(v1);
        String[] categoryCodes = setStrArray(str);
        return categoryCodes;
    }
    
    
    /** 获取一个集合内 指定仓库的   指定年份的  "类别"数组  (编号)    元素唯一*/
    public static String[] getCategoryCodes(String warehouseName,Vector vec,String balanceYear){
        if(vec == null || vec.size() == 0) return null;
        int size = vec.size();
        Vector v1 = new Vector();
        for (int i = 0; i < size; i++) {
            StorageMonthOutPageData data =  (StorageMonthOutPageData)vec.get(i);
            if(data.getWarehouseName().equals(warehouseName)&&data.getBalanceTime().substring(0,4).equals(balanceYear)){
                v1.add(data.getCategoryCode());
            }
        }
        String[] str = setStrArray(v1);
        String[] categoryCodes = setStrArray(str);
        return categoryCodes;
    }
    
    /** 获取一个集合内 指定仓库的   存在有效数据的   "时间"数组  (YYYYMM)*/
    public static String[] getBalanceTimes(String warehouseName,Vector vec){
        if(vec == null || vec.size() == 0) return null;
        int size = vec.size();
        Vector v1 = new Vector();
        for (int i = 0; i < size; i++) {
            StorageMonthOutPageData data =  (StorageMonthOutPageData)vec.get(i);
            if(data.getWarehouseName().equals(warehouseName)){
                v1.add(data.getBalanceTime());
            }
        }
        String[] str = setStrArray(v1);
        String[] balanceTimes = setStrArray(str);
        return balanceTimes;
    }
    
    /** 获取一个集合内 指定仓库的   指定年份的   存在有效数据的   "时间"数组  (YYYYMM)*/
    public static String[] getBalanceTimes(String warehouseName,Vector vec,String balanceTime){
        if(vec == null || vec.size() == 0) return null;
        int size = vec.size();
        Vector v1 = new Vector();
        for (int i = 0; i < size; i++) {
            StorageMonthOutPageData data =  (StorageMonthOutPageData)vec.get(i);
            if(data.getWarehouseName().equals(warehouseName)&&data.getBalanceTime().substring(0, 4).equals(balanceTime)){
                v1.add(data.getBalanceTime());
            }
        }
        String[] str = setStrArray(v1);
        String[] balanceTimes = setStrArray(str);
        return balanceTimes;
    }
    
    /** 去除一个数组重复项 返回一个新的数组  元素唯一*/
    public static String[] setStrArray(String[] str){
        List list = Arrays.asList(str);
        Set set = new HashSet(list);
        String[] temp = new String[set.size()];
        Iterator ite = set.iterator();
        for (int i = 0; ite.hasNext(); i++) {
           temp[i] = (String) ite.next();
        }
        return temp;
    }
    
    /** 使一个集合的值转换为数组*/
    private static String[] setStrArray(Vector vec){
        int size = vec.size();
        String[] str = new String[size];
        for (int i = 0; i < size; i++) {
            str[i] = (String)vec.get(i);
        }
        return str;
    }
    
    /** 获得单一金额结果    通用方法*/
    private static String getResult(String sql,DBConnection con) throws Exception{
        DBResult dbs = DBEngine.doSelect(con, sql);
        ResultSet rs = dbs.getRs();
        String money = "";
        if (rs.next()) {
            double result = rs.getDouble(1);
            money = String.valueOf(result);
        }else{
            money = "0.0";
        }
        rs.close();
        dbs.clear();
        return money;
    }
    
    public static double sumMoney(String warehouseName, String categoryCode, Vector vec) throws Exception{
        double ret = 0;
        
        if(vec == null) return ret;
        if(vec.size() <= 0) return ret;
        if(warehouseName  == null) return ret;
        if(categoryCode == null) return ret;
        
        for(int i = 0; i < vec.size(); i++){
            StorageMonthOutPageData pd = (StorageMonthOutPageData)vec.get(i);
            if(warehouseName.equals(pd.getWarehouseName())){
                if(categoryCode.equals(pd.getCategoryCode())){
                    ret = StringUtil.strToDouble(pd.getProductMoney());
                    break;
                }
            }
        }
        
        return ret;
    }
    
    public static double getSumMoney(String month, Vector vec) throws Exception{
        double ret = 0;
        
        if(vec == null) return ret;
        if(vec.size() <= 0) return ret;
        if(month == null) return ret;
        
        for(int i = 0; i < vec.size(); i++){
            StorageMonthOutPageData pd = (StorageMonthOutPageData)vec.get(i);
            if(month.equals(pd.getBalanceTime())){
                ret = StringUtil.strToDouble(pd.getProductMoney());
                break;
            }
        }
        
        return ret;
    }
    
    public static String sumMoney(DBConnection con,String wareHouseName,String balanceTime,String categoryCode) throws Exception{
        String sql = "select sum(PRODUCT_MONEY) from T_WAREHOUSE w, T_STORAGE_MONTH_OUT s where w.NAME = '"+wareHouseName+"' and w. code = s.WAREHOUSE_CODE and s.BALANCE_TIME = '"+balanceTime+"' and s.category_code = '"+categoryCode+"'";
        return getResult(sql, con);
    }
    
    /** 根据仓库名   结算时间    得到金额合计*/
    public static String sumMoney(DBConnection con,String wareHouseName,String balanceTime)throws Exception{
        String sql = "select sum(PRODUCT_MONEY) from T_WAREHOUSE w, T_STORAGE_MONTH_OUT s where w.NAME = '"+wareHouseName+"' and w. code = s.WAREHOUSE_CODE and s.BALANCE_TIME = '"+balanceTime+"'";
        return getResult(sql, con);
    }
    
    /** 根据仓库名   指定年份    得到金额合计*/
    public static String sumMoney(String wareHouseName,String balanceYear,DBConnection con)throws Exception{
        String sql = "select sum(PRODUCT_MONEY) from T_WAREHOUSE w, T_STORAGE_MONTH_OUT s where w.NAME = '"+wareHouseName+"' and w. code = s.WAREHOUSE_CODE and s.BALANCE_TIME like '"+balanceYear+"%'";
        return getResult(sql, con);
    }
    

    /** 根据仓库名   指定年份    得到金额合计*/
    public static String sumMoneyByYear(String balanceYear,DBConnection con)throws Exception{
        String sql = "select sum(PRODUCT_MONEY) from T_STORAGE_MONTH_OUT where BALANCE_TIME like '"+balanceYear+"%'";
        return getResult(sql, con);
    }
    
    /** 根据仓库名   得到金额合计*/
    public static String sumMoney(DBConnection con,String wareHouseName)throws Exception{
        String sql = "select sum(PRODUCT_MONEY) from T_WAREHOUSE w, T_STORAGE_MONTH_OUT s where w.NAME = '"+wareHouseName+"' and w. code = s.WAREHOUSE_CODE";
        return getResult(sql, con);
    }

    /** 根据仓库名   分类   得到金额合计*/
    public static String sumMoney(String categoryCode,DBConnection con,String wareHouseName)throws Exception{
        String sql = "select sum(PRODUCT_MONEY) from T_WAREHOUSE w, T_STORAGE_MONTH_OUT s where w.NAME = '"+wareHouseName+"' and w. code = s.WAREHOUSE_CODE and s.category_code = '"+categoryCode+"'";
        return getResult(sql, con);
    }
    
    /** 根据  指定日期    得到所有仓库  金额合计*/
    public static String sumMoney(String balanceTime,DBConnection con)throws Exception{
        String sql = "select sum(PRODUCT_MONEY) from T_STORAGE_MONTH_OUT s where s.BALANCE_TIME = '"+balanceTime+"'";
        return getResult(sql, con);
    }
    
    /** 获得仓库    年   月   参数  返回以 仓库编码/年/月/年月 组成的数组*/
    private static String[] getValues(String warehouseCode,String balanceTime){
        if(warehouseCode == null || warehouseCode == "") return null;
        if(balanceTime == null || balanceTime == "") return null;
        if(balanceTime.length() != 6) return null;
        String year = balanceTime.substring(0, 4);
        String month = balanceTime.substring(4, 6);
        String[] values = new String[]{warehouseCode,year,month,balanceTime};
        return values;
    }
    
    /** 判断是否在执行大于等于本年月的数据整理*/
    private static boolean checkBalanceTime(String[] values){
        boolean result = false;
        Date date = new Date();
        int year = date.getYear();
        int month = date.getMonth()+1;
        int s_year = Integer.parseInt( values[1]);
        if(s_year < year){
            result = true;
        }else{
            int s_month = Integer.parseInt( values[2]);
            if(month < s_month){
                result = true;
            }else{
                result = false;
            }
        }
        return result;
    }
    
    /** 查询所要整理的数据是否已存在于表中*/
    private static boolean checkData(DBConnection con,String[] values) throws Exception{
        String sql = "select count(*) from t_storage_month_out where warehouse_code = '"+values[0]+"' and balance_time = '"+values[3]+"'";
        DBResult dbs = DBEngine.doSelect(con, sql);
        ResultSet rs = dbs.getRs();
        String result = "";
        if (rs.next()) {
            result = rs.getString(1);
        }
        rs.close();
        dbs.clear();
        if(Integer.parseInt(result) > 0){
            return false;
        }else{
            return true;
        }
    }
    
    /** 查询老数据*/
    private static Vector selectOldData(DBConnection con,String[] values) throws Exception{
        String[] day = makeTime(values[1], values[2]);
        String sql = "select head.warehouse_code,pro.category_code,sum(sub.product_money) product_money"
            +" from t_out_head head,t_out_sub sub,t_product pro"
            +" where head.out_bill_code = sub.out_bill_code"
            +" and sub.product_code = pro.concentricity_code"
            +" and head.warehouse_code = '"+values[0]+"'"
            +" and head.out_time > '"+day[0]+"'"
            +" and head.out_time < '"+day[1]+"'"
            +" group by head.warehouse_code,pro.category_code ";
        DBResult dbs = DBEngine.doSelect(con, sql);
        ResultSet rs = dbs.getRs();
        Vector vec = new Vector();
        while(rs.next()){
            StorageMonthOutPageData data = new StorageMonthOutPageData();
            data.setWarehouseCode(rs.getString(1), con);
            data.setCategoryCode(rs.getString(2));
            data.setBalanceTime(values[1]+values[2]);
            data.setProductMoney(String.valueOf(rs.getDouble(3)));
            vec.add(data);
        }
        rs.close();
        dbs.clear();
        return vec;
    }
    
    /** 时间组合    根据一个YYYMM   返回一个YYYY-MM-DD HH:MM:SS时间格式的数组  包含两个元素  最小时间    最大时间*/
    private static String[] makeTime(String year,String month)throws Exception{
        String min = "-01 00:00:00";
        String max = " 23:59:59";
        int days = DateUtil.getMaxDayOfMonth(Integer.parseInt(year),Integer.parseInt(month));
        min = year+"-"+month+"min";
        max = year+"-"+month+"-"+String.valueOf(days)+max;
        String[] day = new String[]{min,max};
        return day;
    }
    
    /** 保存数据*/
    public static boolean saveData(DBConnection con,String warehouseCode,String balanceTime)throws Exception{
        boolean isOk = true;
        String[] values = StorageMonthOutSrv.getValues(warehouseCode, balanceTime);
        isOk = StorageMonthOutSrv.checkBalanceTime(values);
        if(!isOk) return isOk;
        isOk = StorageMonthOutSrv.checkData(con, values);
        if(!isOk) return isOk;
        Vector vec = StorageMonthOutSrv.selectOldData(con, values);
        for (int i = 0; i < vec.size(); i++) {
            StorageMonthOutPageData data = (StorageMonthOutPageData)vec.get(i);
            isOk = StorageMonthOutSrv.save(data, con);
            if(!isOk) return isOk;
        }
        return isOk;
    }
    
    private static boolean save(StorageMonthOutPageData data,DBConnection con){
        StorageMonthOut obj;
        obj = StorageMonthOut.fetch();
        boolean result = true;
        try {
            obj.fill(data);
            obj.save(con);
        } catch (Exception e) {
            
            result = false;
            e.printStackTrace();
        }
        return result;
    }
    
    private static double[] sumMoney5009(String warehouseCode,String year,DBConnection con)throws Exception{
        double[] moneys = new double[12];
        DBResult dbs = DBEngine.doSelect(con, getSql5009(warehouseCode, year));
        ResultSet rs = dbs.getRs();

        while (rs.next()) {
            String result = rs.getString(1);
            double money = rs.getDouble(2);
            for(int i = 0; i < moneys.length; i++){
                String smonth;
                smonth = (i + 1) < 10 ? "0" + (i + 1) : (i + 1) + ""; 
                if(smonth.equals(result)){
                    moneys[i] = money;
                }
            }    
           
        }
        rs.close();
        dbs.clear();
        
        return moneys;
    }
    
    private static String getSql5009(String warehouseCode,String year){
        String sql_sub = "select head.warehouse_code,substr(head.out_time, 6, 2) data_month, sub.product_money from t_out_sub sub " +
                            "left outer join t_out_head head on sub.out_bill_code = head.out_bill_code " +
                         "where " +
                            "head.warehouse_code = '" + warehouseCode + "' " +
                            "and substr(head.out_time, 1, 4) = '" + year + "' ";
        
        String sql = "select data_month, sum(product_money) from ( " +
                        sql_sub +
                       ") " +
                       "group by data_month " +
                       "order by data_month";
        return sql;
    }
    
    private static String getSql5011(String year){
        String sql_sub = "select head.warehouse_code,substr(head.out_time, 6, 2) data_month, sub.product_money from t_out_sub sub " +
                            "left outer join t_out_head head on sub.out_bill_code = head.out_bill_code " +
                         "where " +
                            "substr(head.out_time, 1, 4) = '" + year + "' ";
        
        String sql = "select data_month, sum(product_money) from ( " + sql_sub + " ) group by data_month order by data_month";
        
        return sql;
    }
    
    private static double[] sumMoney5011(String year,DBConnection con)throws Exception{
        double[] moneys = new double[12];
        DBResult dbs = DBEngine.doSelect(con, getSql5011(year));
        ResultSet rs = dbs.getRs();

        while (rs.next()) {
            String result = rs.getString(1);
            double money = rs.getDouble(2);
            for(int i = 0; i < moneys.length; i++){
                String smonth;
                smonth = (i + 1) < 10 ? "0" + (i + 1) : (i + 1) + ""; 
                if(smonth.equals(result)){
                    moneys[i] = money;
                }
            }    
           
        }
        rs.close();
        dbs.clear();
        
        return moneys;
    }
    
    private static String[] sumMoney5012(String year,String categoryCode,DBConnection con)throws Exception{
        String[] moneys = new String[12];
        DBResult dbs = DBEngine.doSelect(con, getSql5012(year,categoryCode));
        ResultSet rs = dbs.getRs();
        int i = 0;
        while (rs.next()) {
            double result = rs.getDouble(1);
            moneys[i] = String.valueOf(result) == null?"0.00":String.valueOf(result);
            i++;
        }
        rs.close();
        dbs.clear();
        if(i != 12){
            return null;
        }
        return moneys;
    }
    
    
    private static String getSql5012(String year,String categoryCode){
        String sql = " select sum(PRODUCT_MONEY) from T_STORAGE_MONTH_OUT where BALANCE_TIME = '";
        sql += year+"01' and CATEGORY_CODE = '"+categoryCode+"'";
        for (int i = 2; i <= 12; i++) {
            sql += " union all ";
            sql += " select sum(PRODUCT_MONEY) from T_STORAGE_MONTH_OUT where BALANCE_TIME = '";
            if(i < 10){
                sql += year+"0"+i+"'";
            }else{
                sql += year+i+"'";
            }
            sql += " and CATEGORY_CODE = '"+categoryCode+"'";
        }
        return sql;
    }
    
    
    private static String[] getCategoryCodes(String year,DBConnection con)throws Exception{
        String sql = "select CATEGORY_CODE from T_STORAGE_MONTH_OUT where BALANCE_TIME like '"+year+"%' group by category_code";
        DBResult dbs = DBEngine.doSelect(con, sql);
        ResultSet rs = dbs.getRs();
        Vector vec = new Vector();
        while (rs.next()) {
            vec.add(rs.getString(1));
        }
        rs.close();
        dbs.clear();
        String[] categoryCodes = new String[vec.size()];
        for (int i = 0; i < vec.size(); i++) {
            categoryCodes[i] = (String)vec.get(i);
        }
        return categoryCodes;
    }
    
    private static String[] getCategoryCodes(String warehouseCode,String year,DBConnection con)throws Exception{
        String sql = "select CATEGORY_CODE from T_STORAGE_MONTH_OUT where BALANCE_TIME like '"+year+"%' and WAREHOUSE_CODE = '"+warehouseCode+"' group by category_code";
        DBResult dbs = DBEngine.doSelect(con, sql);
        ResultSet rs = dbs.getRs();
        Vector vec = new Vector();
        while (rs.next()) {
            vec.add(rs.getString(1));
        }
        rs.close();
        dbs.clear();
        String[] categoryCodes = new String[vec.size()];
        for (int i = 0; i < vec.size(); i++) {
            categoryCodes[i] = (String)vec.get(i);
        }
        return categoryCodes;
    }
    
    private static String[] sumMoney5010(String year,String categoryCode,DBConnection con,String wareHouseCode)throws Exception{
        String[] moneys = new String[12];
        DBResult dbs = DBEngine.doSelect(con, getSql5010(year,wareHouseCode,categoryCode));
        ResultSet rs = dbs.getRs();
        int i = 0;
        while (rs.next()) {
            double result = rs.getDouble(1);
            moneys[i] = String.valueOf(result) == null?"0.00":String.valueOf(result);
            i++;
        }
        rs.close();
        dbs.clear();
        if(i != 12){
            return null;
        }
        return moneys;
    }
    
    private static String getSql5010(String year,String warehouseCode,String categoryCode){
        String sql = " select sum(PRODUCT_MONEY) from T_STORAGE_MONTH_OUT where WAREHOUSE_CODE = '"+warehouseCode+"' and BALANCE_TIME = '";
        sql += year+"01' and CATEGORY_CODE = '"+categoryCode+"'";
        for (int i = 2; i <= 12; i++) {
            sql += " union all ";
            sql += " select sum(PRODUCT_MONEY) from T_STORAGE_MONTH_OUT where WAREHOUSE_CODE = '"+warehouseCode+"' and BALANCE_TIME = '";
            if(i < 10){
                sql += year+"0"+i+"'";
            }else{
                sql += year+i+"'";
            }
            sql += " and CATEGORY_CODE = '"+categoryCode+"'";
        }
        return sql;
    }
    
    /** 对 StorageMonthOut  数据  进行处理    */
    public static boolean makeStorageMonthOutData(DBConnection con,String balanceTime1,String balanceTime2){
        if(deleteOldStorageMonthOutData(con,balanceTime1)){
            if(insertNewStorageMonthOut(con,balanceTime2)){
                return true;
            }else{
                return false;
            }
        }else{
            return false;
        }
    }
    
    /** 删除已有数据  */
    private static boolean deleteOldStorageMonthOutData(DBConnection con,String balanceTime){
        DBPreparedStatement dbp = null;
       try {
        String sql = "delete t_storage_month_out where balance_time >= " + balanceTime;
           dbp = con.prepareStatement(sql);
           int i = dbp.executeUpdate();
           if(i > 0){
               return true;
           }else{
               return false;
           }
        } catch (Exception e) {
            
            e.printStackTrace();
            return false;
        }finally{
            if(dbp != null){
                try {
                    dbp.close();
                } catch (Exception e) {
                    
                    e.printStackTrace();
                }
            }
        }
    }
    
    /** 数据整理    */
    private static boolean insertNewStorageMonthOut(DBConnection con,String balanceTime){
        String sql = " select "+
        " sys_guid() id, "+
        " warehouse_code, "+
        " balance_time, "+
        " category_code, "+
        " sum(product_money) product_money "+
      " from "+
      " ( "+
      " select "+
        " head.warehouse_code, "+
        " (substr(head.out_time, 1, 4)||substr(head.out_time, 6, 2) ) BALANCE_TIME, "+
        " (select substr(category_code,1,1) from t_product pro where pro.id = sub.product_id) category_code, "+
        " sub.product_money "+
      " from "+
       " t_out_head head, "+
       " t_out_sub sub "+
      " where "+
       " head.id = sub.out_bill_id "+
       " and head.out_time > '"+balanceTime+"' "+
      " ) a "+
      " group by "+
         " a.warehouse_code, "+
         " a.category_code, "+
         " a.balance_time "+
      " order by "+
         " warehouse_code, "+
         " balance_time ";
        DBResult dbs = null;
        ResultSet rs = null;
        try {
            dbs = DBEngine.doSelect(con, sql);
            rs = dbs.getRs();
            while(rs.next()){
                StorageMonthOutPageData data = new StorageMonthOutPageData();
                data.setId(rs.getString(1));
                data.setWarehouseCode(rs.getString(2),con);
                data.setBalanceTime(rs.getString(3));
                data.setCategoryCode(rs.getString(4));
                data.setProductMoney(String.valueOf(rs.getDouble(5)));
                boolean result = StorageMonthOutSrv.save(data, con);
                if(!result){
                    return false;
                }
            }
            return true;
        } catch (Exception e) {
            
            e.printStackTrace();
            return false;
        }finally{
            try {
                rs.close();
                dbs.clear();
            } catch (SQLException e) {
                
                e.printStackTrace();
            } catch (Exception e) {
                
                e.printStackTrace();
            }
        }
    }
}





